const router = require("koa-router")();
const moment = require("moment");
const xlsx = require("node-xlsx");

/**
 * api {get} /home/app/schema/relation 关联列表
 *
 * apiParam {Number} page 页码
 * apiParam {String} table 数据表
 * apiParam {String} relation 关联关系
 * apiParam {String} target 关联表
 * apiParam {String} foreign_key_target 外键关联表
 *
 */
router.get("/relation", async (ctx, nex) => {
  const user = ctx.user;
  const baas = ctx.baas;

  const foreignKeyTarget = ctx.query.foreign_key_target;
  const { page, table, target, relation } = ctx.query;

  const data = await BaaS.Models.relation
    .query(qb => {
      qb.where("baas_id", "=", baas.id);
      if (table) {
        qb.where("table", "=", table);
      }
      if (target) {
        qb.where("target", "=", target);
      }
      if (relation) {
        qb.where("relation", "=", relation);
      }
      if (foreignKeyTarget) {
        qb.where("foreign_key_target", "=", foreignKeyTarget);
      }
      qb.orderBy("id", "desc");
    })
    .fetchPage({
      pageSize: ctx.config("pageSize"),
      page: page,
      withRelated: []
    });

  ctx.success(data, "关联列表");
});
/**
 * api {get} /home/app/schema/info/relation 关联搜索
 *
 *
 */
router.get("/info/relation", async (ctx, nex) => {
  const user = ctx.user;
  const baas = ctx.baas;
  const relation = [
    "hasOne",
    "hasMany",
    "belongsTo",
    "belongsToMany",
    "morphTo",
    "morphOne",
    "morphMany"
  ];
  // 数据表
  const table = await ctx.table(baas.database); // 数据表

  const data = {
    relation: relation,
    table: table
  };

  ctx.success(data, "字段额外信息");
});
/**
 * api {get} /home/app/schema/relation/field/:tableName 查询表字段
 *
 *
 */
router.get("/relation/field/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  const sql =
    "show full fields from `" + baas.database + "`.`" + tableName + "`";
  const field = await ctx.bookshelf.knex.raw(sql);

  ctx.success(field[0], "数据表字段");
});
/**
 * api {get} /home/app/schema/export/relation 导出关联关系
 *
 * apiParam {String} table 数据表
 * apiParam {String} relation 关联关系
 * apiParam {String} target 关联表
 * apiParam {String} foreign_key_target 外键关联表
 *
 */
router.get("/export/relation", async (ctx, nex) => {
  const baas = ctx.baas;

  const foreignKeyTarget = ctx.query.foreign_key_target;
  const { table, target, relation } = ctx.query;

  const relationList = await BaaS.Models.relation
    .query(qb => {
      qb.where("baas_id", "=", baas.id);
      if (table) {
        qb.where("table", "=", table);
      }
      if (target) {
        qb.where("target", "=", target);
      }
      if (relation) {
        qb.where("relation", "=", relation);
      }
      if (foreignKeyTarget) {
        qb.where("foreign_key_target", "=", foreignKeyTarget);
      }
      qb.orderBy("id", "desc");
    })
    .fetchAll();

  const rows = new Array();
  rows[0] = [
    "数据表",
    "关联表",
    "关联昵称，默认关联表",
    "关联关系",
    "外键",
    "外键关联表",
    "创建时间",
    "更新时间"
  ];
  for (const key in relationList) {
    rows.push([
      relationList[key].table,
      relationList[key].target,
      relationList[key].targeted,
      relationList[key].relation,
      relationList[key].foreign_key,
      relationList[key].foreign_key_target,
      relationList[key].created_at,
      relationList[key].updated_at
    ]);
  }

  const name = moment().format("YYYY-MM-DD HH:mm:ss");
  const buffer = xlsx.build([
    {
      name: name,
      data: rows
    }
  ]);
  ctx.set("Content-disposition", "attachment; filename=" + name + ".xlsx");
  ctx.body = buffer;
});

/**
 * api {get} /home/app/schema/relation/:id 获取单个关联
 *
 * apiParam {Number} id 关联表id
 *
 */
router.get("/relation/:id", async (ctx, nex) => {
  const baas = ctx.baas;
  const { id } = ctx.params;

  const data = await BaaS.Models.relation
    .query(qb => {
      qb.where("id", "=", id);
      qb.where("baas_id", "=", baas.id);
    })
    .fetch();

  ctx.success(data, "单个关联信息");
});
/**
 * api {post} /home/app/schema/add/relation 新增修改关联
 * 
 * apiParam {
	table: 数据表
	target: 关联表
	relation: 关联关系
	targeted: 关联昵称
	foreign_key: 外键
	foreign_key_target: 外键关联表
 } 
 * 
 */
router.post("/add/relation", async (ctx, nex) => {
  const baas = ctx.baas;

  const foreignKeyTarget = ctx.post.foreign_key_target;
  const foreignKey = ctx.post.foreign_key;
  const { id, table, target, relation, targeted } = ctx.post;

  const result = await BaaS.Models.relation
    .forge({
      id: id,
      baas_id: baas.id,
      table: table,
      target: target,
      relation: relation,
      targeted: targeted,
      foreign_key: foreignKey,
      foreign_key_target: foreignKeyTarget
    })
    .save();

  // 删除redis缓存
  await BaaS.redis.delAll(
    `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:relations`
  );

  ctx.success(result, "保存成功");
});
/**
 * api {get} /home/app/schema/del/relation/:id 删除关联
 *
 * apiParam {Number} id 关联表id
 *
 */
router.get("/del/relation/:id", async (ctx, nex) => {
  const baas = ctx.baas;
  const { id } = ctx.params;

  const relation = await BaaS.Models.relation
    .query(qb => {
      qb.where("id", "=", id);
      qb.where("baas_id", "=", baas.id);
    })
    .fetch();
  if (!relation) {
    ctx.error("", "参数错误");
    return;
  }

  const result = await BaaS.Models.relation
    .forge({
      id: id
    })
    .destroy();

  // 删除redis缓存
  await BaaS.redis.delAll(
    `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:relations`
  );

  ctx.success(result, "删除关联");
});

module.exports = router;
